﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using Models;

namespace DAL
{
    public class NewsCategoryInfoDAL:INewsCategoryInfo
    {
        private const string SQL_ALL_NewsCategoryInfo = @"select * from NewsCategoryInfo where CategoryID in
                                                       (select c.CategoryID from (select sum(CategoryID) as CCount, CategoryID from NewsInfo
                                                       group by CategoryID) c where c.CCount > 0)";
        private const string SQL_One_NewsCategoryInfo = @"select * from NewsCategoryInfo where CategoryID = ";

        public DataBase DB = new DataBase();

        public List<NewsCategoryInfo> GetAllNewsCategoryInfo()
        {
            List<NewsCategoryInfo> list = new List<NewsCategoryInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_ALL_NewsCategoryInfo);
            while (reader.Read())
            {
                NewsCategoryInfo newscategoryInfo = new NewsCategoryInfo();
                ReadtoNewsCategoryInfo(reader, newscategoryInfo);
                list.Add(newscategoryInfo);
            }
            DB.CloseDB();
            return list;
        }
        public List<NewsCategoryInfo> GetAdminNewsCategoryInfo()
        {
            List<NewsCategoryInfo> list = new List<NewsCategoryInfo>();
            DB.DatabaseConnection();
            string sql = "SELECT * FROM NewsCategoryInfo ";
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                NewsCategoryInfo nc = new NewsCategoryInfo();
                ReadtoNewsCategoryInfo(reader, nc);
                list.Add(nc);
            }
            DB.CloseDB();
            return list;
        }
        public void ReadtoNewsCategoryInfo(SqlDataReader reader, NewsCategoryInfo newscategoryInfo)
        {
            try
            {
                newscategoryInfo.CategoryID = Int32.Parse(reader["CategoryID"].ToString());
            }
            catch
            { }
            newscategoryInfo.CategoryName =reader["CategoryName"] is DBNull?"":reader["CategoryName"].ToString();
            try
            {
                newscategoryInfo.HasNNews = Int32.Parse(reader["HasNNews"].ToString());
            }
            catch
            { }
        }
        public NewsCategoryInfo GetOneNewsCategoryInfo(int CategoryID)
        {
            NewsCategoryInfo newscategoryInfo = new NewsCategoryInfo();

            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_One_NewsCategoryInfo+CategoryID.ToString());
            if (reader != null && reader.HasRows && reader.Read())
            {
                ReadtoNewsCategoryInfo(reader, newscategoryInfo);
            }
            DB.CloseDB();
            return newscategoryInfo;
        }
        public int AddNewsCategoryInfo(NewsCategoryInfo newsCategoryInfo)
        {
            int isSuccess = -1;
            string sql = @"INSERT INTO NewsCategoryInfo([CategoryName],[HasNNews]) 
                                    VALUES('" + newsCategoryInfo.CategoryName + "'," + newsCategoryInfo.HasNNews + ")";

            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public int UpdateNewsCategoryInfo(NewsCategoryInfo newscategory)
        {
            int isSucess = -1;
            string sql = @"UPDATE NewsCategoryInfo SET [CategoryName] = '" + newscategory.CategoryName + "' WHERE CategoryID = " + newscategory.CategoryID + " ";
            isSucess = DB.UpdateRecords(sql);
            return isSucess;
        }
        public int DeleteNewsCategoryInfo(int CategoryID)
        {
            int isSucess = -1;
            string sql = @"DELETE FROM NewsCategoryInfo WHERE CategoryID = " + CategoryID;
            isSucess = DB.UpdateRecords(sql);
            return isSucess;
        }
        public int UpdateCategoryHasNews(int CategoryID, int flag)
        {
            int isSuccess = -1;
            string sql = @"UPDATE NewsCategoryInfo SET HasNNews = " + flag + " WHERE CategoryID = " + CategoryID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
    }
}
